package top.wardyan.sqlchecker.business;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.nio.charset.Charset;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.function.Consumer;
import java.util.function.Predicate;

import com.alibaba.druid.filter.stat.StatFilter;
import com.jfinal.aop.Aop;
import com.jfinal.kit.Prop;
import com.jfinal.kit.PropKit;
import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.CaseInsensitiveContainerFactory;
import com.jfinal.plugin.activerecord.Config;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.dialect.SqlServerDialect;
import com.jfinal.plugin.druid.DruidPlugin;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.io.file.FileReader;
import cn.hutool.core.io.resource.ResourceUtil;
import cn.hutool.core.lang.Console;
import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ReUtil;
import cn.hutool.core.util.RuntimeUtil;
import cn.hutool.core.util.URLUtil;
import cn.hutool.log.Log;
import cn.hutool.log.LogFactory;
import top.wardyan.base.common.config.ConfigProp;
import top.wardyan.base.common.util.EncodingDetect;
import top.wardyan.base.db.util.ActiveRecordUtil;
//import top.wardyan.common.db.kits.DbQryKit;
import top.wardyan.common.util.DateUtil;
import top.wardyan.sqlchecker.model.YTSqlChecker;
import top.wardyan.sqlchecker.model.YTSqlCheckerDuplicate;
import top.wardyan.sqlchecker.model.YTSqlCheckerItem;
import top.wardyan.sqlchecker.model.YTSqlfile;
import top.wardyan.sqlchecker.po.WSqlFile;
import top.wardyan.sqlchecker.service.IYTSqlCheckerDuplicateService;
import top.wardyan.sqlchecker.service.IYTSqlCheckerItemService;
import top.wardyan.sqlchecker.service.IYTSqlCheckerService;
import top.wardyan.sqlchecker.service.IYTSqlfileService;
import top.wardyan.sqlchecker.util.CustomizedReadSQLFileUtil;
import top.wardyan.wtask.feishu.api.FeishuMsgBusiness;
import top.wardyan.wtask.service.IProcService;
/**
 * SQL检查工具
 * 步骤:
 * 生成目录
 * 还原数据库,创建需要的数据表
 * 检查脚本
 * 运行脚本
 * 
 * @author harrydeep@wardyan.top
 * 2022年10月3日
 */
@SuppressWarnings("serial")
public class SQLCheckerBusiness {
	
	private static final Log log = LogFactory.get();
	
	private static final String CONFIGFILENAME = "config-sqlchecker.txt";
	
	private Prop PROP = PropKit.use(CONFIGFILENAME);
	private String ADDR = PROP.get("addr");
	private Integer PORT = PROP.getInt("port");
	private String USERNAME = PROP.get("username");
	private String PASSWORD = PROP.get("password");
	private String DBNAME = PROP.get("dbname");
	
	// private static final String BAKNAME = "dbdata/ERP数据库.QL.20220226.CLEAN.bak";
	private String BAKNAME = PROP.get("bakname");
	/** SQL文件本地存放路径 **/
	private String SQLFOLDER = PROP.get("sqlfolder");
	private String MAXAGE = PROP.get("maxage");
	private String SQLSERVER = PROP.get("sqlserver");
	private String SOFTWAREVERSION = PROP.get("softwareversion");
	/** 执行SQL和脚本以从哪个时间点开始 **/
	private long BEGINTIME = cn.hutool.core.date.DateUtil.parse(MAXAGE).getTime();
	private static final String CONFIG = "sqlchecker";
	private static final char PREFIX_char = 65279;
	private static final String PREFIX = PREFIX_char + "go"; //char d = 65279;
	
	
	private final Prop CONFIGS = ConfigProp.load();
	private final String FEISHU_CHATID = CONFIGS.get("sqlchecker.feishu.chatid", "");

	private final Boolean ENABLEMSG = CONFIGS.getBoolean("sqlchecker.sendmsg", false);
	//sqlchecker.sendmsg

	String SQL_RESTORE1 = "IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE name = 'ERPDBTEST') ALTER DATABASE ERPDBTEST SET  SINGLE_USER WITH ROLLBACK IMMEDIATE";
	String SQL_RESTORE2 = "IF EXISTS(SELECT 1 FROM sys.databases AS d WHERE name = 'ERPDBTEST') DROP DATABASE ERPDBTEST";
	String SQL_RESTORE3 = "RESTORE DATABASE ERPDBTEST FROM  DISK = N'?' WITH  FILE = 1, NOUNLOAD,  STATS = 5";
	String SQL_DROPSQLFILESLIST = "IF EXISTS(SELECT 1 FROM sys.tables AS t WHERE name = 'yt_sqlfileslist') DROP TABLE dbo.yt_sqlfileslist";
	String SQL_CREATESQLFILESLIST = "CREATE TABLE dbo.yt_sqlfileslist(sID INT IDENTITY(1, 1), xAuthor NVARCHAR(100), xDate NVARCHAR(100), xName NVARCHAR(200), xPath NVARCHAR(2000), xMD5 NVARCHAR(100), xLocation NVARCHAR(500))";
	/** 检查是否同一天提交 **/
	String SQL_DUMPLICATESUBMIT = """
SELECT a.xObjectName, a.xDate , y.xOwner, y.xMD5
	, STUFF(( SELECT ',' + ISNULL(x.xOwner, '') FROM dbo.yt_sqlchecker_item AS x (NOLOCK)  WHERE x.mID = a.mID AND x.xDate = a.xDate AND x.xObjectName = a.xObjectName FOR XML PATH('')), 1, 1, '') AS xAuthor
FROM (
	SELECT n.xObjectName, n.xDate, n.mID
	FROM dbo.yt_sqlchecker_item AS n (NOLOCK) 
	WHERE n.mID = ? 
	AND ISNULL(n.xObjectName, '') <> '' 
	GROUP BY n.xObjectName, n.xDate, n.mID
	HAVING COUNT(DISTINCT n.xOwner) > 1
	AND MAX(n.xMD5) <> MIN(n.xMD5)
) AS a
LEFT JOIN dbo.yt_sqlchecker_item AS y (NOLOCK) ON y.mID = a.mID AND y.xDate = a.xDate AND y.xObjectName = a.xObjectName
LEFT JOIN dbo.yt_sqlfile AS s (NOLOCK) ON s.xMD5 = y.xMD5
WHERE s.xStatus < 2
ORDER BY a.xDate, a.xObjectName
""";
	
	/** 执行每个脚本的耗时, 毫秒 **/
	Map<String, Long> costs = new LinkedHashMap<String, Long>();
	

	private IYTSqlfileService sqlfileService = Aop.get(IYTSqlfileService.class);
	private IYTSqlCheckerService sqlCheckerService = Aop.get(IYTSqlCheckerService.class);
	private IYTSqlCheckerItemService sqlCheckerItemService = Aop.get(IYTSqlCheckerItemService.class);
	private IYTSqlCheckerDuplicateService sqlCheckerDuplicateService = Aop.get(IYTSqlCheckerDuplicateService.class);
	private IProcService procService = Aop.get(IProcService.class);
	private FeishuMsgBusiness feishuMsgBusiness = Aop.get(FeishuMsgBusiness.class);
	/**
	 * 生成所需要的文件夹
	 * 备份文件放在resources目录下的dbdata目录里
	 */
	private void generateFolder() {
		

	}
	
	
	DruidPlugin dps = null;
	ActiveRecordPlugin arps = null;

	/**
	 * 重建数据库和数据表
	 */
	private void restoredb() {
		//var u = ResourceUtil.getResource(BAKNAME);
		// System.out.println(u);
		
		// 备份文件改为从服务器上取
		/**
		String path = u.getPath();
		if(path.startsWith("/")) {
			path = path.substring(1);
		}
		path = URLUtil.decode(path);
		// System.out.println(path);
		**/
		String path = "E:\\UUERP\\SQLSERVER2022\\BAKS2022\\ERPDBTEST.clean.20241203.bak";
		Record r = Db.findFirst("select xValue from yt_setting (nolock) where sID = 150002");
		if(r != null) {
			path = r.getStr("xValue");
		}
		
		try {
			log.info("准备数据库环境...");
			
			// 先检查这个CONFIG是否已存在
			Config c = DbKit.getConfig(CONFIG);
			if(c != null) {
				DbKit.removeConfig(CONFIG);
			}
			
			String jdbcurl = String.format("jdbc:sqlserver://%s:%d; DatabaseName=%s; trustServerCertificate=true", ADDR, PORT, "master");	// 还原数据库要先连master
			DruidPlugin dp = new DruidPlugin(jdbcurl, USERNAME, PASSWORD);
			dp.addFilter(new StatFilter());
			ActiveRecordPlugin arp=new ActiveRecordPlugin(CONFIG, dp);
			arp.setContainerFactory(new CaseInsensitiveContainerFactory());
			arp.setDialect(new SqlServerDialect());	
			dp.start();
			arp.start();
			
			
			Db.use(CONFIG).update(SQL_RESTORE1);
			Db.use(CONFIG).update(SQL_RESTORE2);
			String s = SQL_RESTORE3.replace("?", path);
			Db.use(CONFIG).update(s);
			
			arp.stop();
			dp.stop();
			ActiveRecordUtil.init(CONFIG, 1, ADDR, PORT, DBNAME, USERNAME, PASSWORD);
			
			Db.use(CONFIG).update(SQL_DROPSQLFILESLIST);
			Db.use(CONFIG).update(SQL_CREATESQLFILESLIST);
			// System.out.println(url);
			
			log.info("准备数据库环境完成");
		}catch(Exception e) {
			log.error(e);
		}
	}

	/**
	 * 从服务器上拷贝脚本
	 * 2022-10-30 先从发版本的地方拷贝, 再从未发版本的地方拷贝, 然后把
	 */
	private void copySQLs() {
		// 检测是在windows还是在linux下,如果是在windows下则调用copySQLsWin,如果是在linux下则调用copySQLsLin
		String os = System.getProperty("os.name").toLowerCase();
		if (os.contains("win")) {
			this.copySQLsWin();
		} else if (os.contains("nix") || os.contains("nux") || os.contains("aix")) {
			this.copySQLsLin();
		} else {
			// 处理其他操作系统的情况，或者抛出异常
			throw new UnsupportedOperationException("Unsupported operating system: " + os);
		}
	}
	
	
	
	
	public void copySQLsLin(){
		// SQL目录还不存在的话先创建目录
		var u = ResourceUtil.getResource(BAKNAME);
		// System.out.println(u);
		String path = u.getPath();
		if(path.startsWith("/")) {
			path = path.substring(1);
		}
		path = URLUtil.decode(path);
		String[] ps = path.split("/");
		ps[ps.length - 1] = SQLFOLDER;
		
		String p = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		// 发版本里未找到的脚本
		ps[ps.length - 1] = "OKS";
		String un = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		
		// Console.log(ArrayUtil.join(ps, "/"));

		if(FileUtil.exist(p)) {
			log.info("原目录已存在,删除重来:{}", p);
			// FileUtil.mkdir(p);
			long before = System.currentTimeMillis();
			var ok = FileUtil.del(p);
			
			log.info("删除原目录完成,耗时秒:{}", (System.currentTimeMillis() - before) / 1000);
			
		}

		if(FileUtil.exist(un)) {
			log.info("原目录已存在,删除重来:{}", un);
			// FileUtil.mkdir(p);
			long before = System.currentTimeMillis();
			var ok = FileUtil.del(un);
			
			log.info("删除原目录完成,耗时秒:{}", (System.currentTimeMillis() - before) / 1000);
			
		}
		
		if(!FileUtil.exist(p)) {
			log.info("创建文件夹:", p);
			FileUtil.mkdir(p);
		}
		
		long before = System.currentTimeMillis();
		// 20250209 不再从这里检查.只从提交目录里检查.这样的话老总也得提交到这里了
//				// 已发版本的
		// String version = "robocopy \"" + SOFTWAREVERSION + "\" \"" + p + "\"  *.sql /s /MIR /MT:4 /maxage:" + MAXAGE;
		// rsync -raz sqlcheck/remotes/* sqlcheck/locals
		// 部署在linux下, 需要先映射: /mnt/submiteds, /mnt/all
		//String version = "robocopy \"" + SOFTWAREVERSION + "\" \"" + un + "\"  *.sql /s /MT:4 /maxage:" + MAXAGE ;		// /MIR
		String version = "rsync -raz /mnt/submiteds/* " + un;
//				
		log.info("执行命令:{}", version);
		
		//String str = RuntimeUtil.execForStr (version);
//		Process process;
//		try {
//			process = Runtime.getRuntime().exec(version);
//			process.waitFor();
//		} catch (IOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (InterruptedException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		// 创建一个ProcessBuilder对象
		ProcessBuilder pb = new ProcessBuilder();
		// 设置要执行的shell命令
		pb.command("bash", "-c", version);
		// 执行shell命令
		try {
			Process process = pb.start();
			// 等待命令执行结束
			process.waitFor();
		} catch (IOException | InterruptedException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		log.info("执行命令完成:{},耗时秒:{}", version, (System.currentTimeMillis() - before) / 1000);
//				
		
		// String cmd = "robocopy \"\\\\server6\\sql\\MES_SQL_XH \"" + p + "\"  *.sql /s /MIR /maxage:" + MAXAGE;
		// SQLSERVER
		// 排除已打包脚本, 排除debug脚本
		// String cmd = "robocopy \"" + SQLSERVER + "\" \"" + p + "\" *.sql /s /MIR /MT:4 /maxage:" + MAXAGE + "  /XD 22* 23* 24* 25* 26* 27* 28* 29* 21* 1* 2000* 3* /XF *debug*";
		// String cmd = "robocopy \"" + SQLSERVER + "\" \"" + p + "\" *.sql /s /MT:4 /maxage:" + MAXAGE + "  /XF *debug*";		// /MIR
		String cmd = "rsync -raz /mnt/all/* " + un;
		
		log.info("执行命令:{}", cmd);
		before = System.currentTimeMillis();
		RuntimeUtil.execForStr (cmd);
		log.info("执行命令完成:{},耗时秒:{}", cmd, (System.currentTimeMillis() - before) / 1000);
		// String rs = str;
		
		this.move(un, p);
	}
	

	/**
	 * 从服务器上拷贝脚本
	 * 2022-10-30 先从发版本的地方拷贝, 再从未发版本的地方拷贝, 然后把
	 */
	private void copySQLsWin() {
		// SQL目录还不存在的话先创建目录
		var u = ResourceUtil.getResource(BAKNAME);
		// System.out.println(u);
		String path = u.getPath();
		if(path.startsWith("/")) {
			path = path.substring(1);
		}
		path = URLUtil.decode(path);
		String[] ps = path.split("/");
		ps[ps.length - 1] = SQLFOLDER;
		
		String p = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		// 发版本里未找到的脚本
		ps[ps.length - 1] = "OKS";
		String un = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		
		// Console.log(ArrayUtil.join(ps, "/"));

		if(FileUtil.exist(p)) {
			log.info("原目录已存在,删除重来:{}", p);
			// FileUtil.mkdir(p);
			long before = System.currentTimeMillis();
			var ok = FileUtil.del(p);
			
			log.info("删除原目录完成,耗时秒:{}", (System.currentTimeMillis() - before) / 1000);
			
		}

		if(FileUtil.exist(un)) {
			log.info("原目录已存在,删除重来:{}", un);
			// FileUtil.mkdir(p);
			long before = System.currentTimeMillis();
			var ok = FileUtil.del(un);
			
			log.info("删除原目录完成,耗时秒:{}", (System.currentTimeMillis() - before) / 1000);
			
		}
		
		if(!FileUtil.exist(p)) {
			log.info("创建文件夹:", p);
			FileUtil.mkdir(p);
		}
		
		long before = System.currentTimeMillis();
		// 20250209 不再从这里检查.只从提交目录里检查.这样的话老总也得提交到这里了
//		// 已发版本的
		// String version = "robocopy \"" + SOFTWAREVERSION + "\" \"" + p + "\"  *.sql /s /MIR /MT:4 /maxage:" + MAXAGE;
		String version = "robocopy \"" + SOFTWAREVERSION + "\" \"" + un + "\"  *.sql /s /MT:4 /maxage:" + MAXAGE ;		// /MIR
//		
		log.info("执行命令:{}", version);
		
		String str = RuntimeUtil.execForStr (version);
		log.info("执行命令完成:{},耗时秒:{}", version, (System.currentTimeMillis() - before) / 1000);
//		
		
		// String cmd = "robocopy \"\\\\server6\\sql\\MES_SQL_XH \"" + p + "\"  *.sql /s /MIR /maxage:" + MAXAGE;
		// SQLSERVER
		// 排除已打包脚本, 排除debug脚本
		// String cmd = "robocopy \"" + SQLSERVER + "\" \"" + p + "\" *.sql /s /MIR /MT:4 /maxage:" + MAXAGE + "  /XD 22* 23* 24* 25* 26* 27* 28* 29* 21* 1* 2000* 3* /XF *debug*";
		String cmd = "robocopy \"" + SQLSERVER + "\" \"" + p + "\" *.sql /s /MT:4 /maxage:" + MAXAGE + "  /XF *debug*";		// /MIR 
		
		log.info("执行命令:{}", cmd);
		before = System.currentTimeMillis();
		str = RuntimeUtil.execForStr (cmd);
		log.info("执行命令完成:{},耗时秒:{}", cmd, (System.currentTimeMillis() - before) / 1000);
		// String rs = str;
		
		this.move(un, p);
	}
	
	
	private void move(String from, String to) {
		File f = new File(from);
		if(f != null) {
			File[] subs = f.listFiles();
			if(subs != null && subs.length > 0) {
				for(File s : subs) {
					this.move(from + "/" + s.getName(), to);
				}
			}
			if(f.isDirectory() && f.getName().contains("s")) {
				File target = new File(to);
				log.info("转移目录:{}, {}", f.getPath(), target.getPath());
				FileUtil.move(f, target, true);
			}
		}
	}
	

	/**
	 * 检查同一天重复提交的脚本
	 */
	private void checkDumplicateSubmit(Integer mID, List<WSqlFile> sfs) {
		List<Record> rs = Db.find(SQL_DUMPLICATESUBMIT, mID);
		if(rs != null) {
			for(Record r : rs) {
				Timestamp date = new Timestamp(r.getDate("xDate").getTime());
				String md5 = r.getStr("xMD5");
				String owner = r.getStr("xOwner");
				String msg = r.getStr("xAuthor");
				YTSqlCheckerDuplicate item = new YTSqlCheckerDuplicate();
				item.setMID(mID)
				.setXObjectName(r.getStr("xObjectName"))
				// .setXDate(r.getTimestamp("xDate"))
				.setXDate(date)
				.setXOwner(owner)
				.setXMsg(msg)
				.set("xStatus", 1)
				;
				sqlCheckerDuplicateService.save(item);
				// 执行失败就无所谓冲突了
				String sql = "UPDATE dbo.yt_sqlchecker_item SET xDataType = 3 WHERE mID = ? AND xDate = ? AND xOwner = ? AND xObjectName = ? AND ISNULL(xDataType, 0) <> 4"  ;
				Db.update(sql, mID, date, r.getStr("xOwner"), r.getStr("xObjectName"));
				
				sfs.stream().filter(new Predicate<WSqlFile>() {

					@Override
					public boolean test(WSqlFile t) {
						return t.getMd5().equals(md5) && t.getDate().getTime() == date.getTime() && t.getOwner().equals(owner);
					}
					
				}).forEach(new Consumer<WSqlFile>() {

					@Override
					public void accept(WSqlFile t) {
						if(t.getStatus() != null && t.getStatus() == 2) {
							// 之前标记过处理了 不管了
							// sqlCheckerDuplicateService.save(item);
							sqlCheckerDuplicateService.delete(item);
							String str = "UPDATE dbo.yt_sqlchecker_item SET xDataType = 1 WHERE mID = ? AND xDate = ? AND xOwner = ? AND xObjectName = ? AND ISNULL(xDataType, 0) = 3"  ;
							Db.update(str, mID, date, r.getStr("xOwner"), r.getStr("xObjectName"));
						}else {
							t.setDatatype(3);
							List<String> errors = t.getErrors();
							errors.add("当天提交冲突:" + msg);
						}
					}
					
				});
			}
		}
	}
	
	
	private void execSQLFiles(List<WSqlFile> ls) {
		if(ls == null) {
			ls = new ArrayList<>();
		}
		for(WSqlFile f : ls) {
			this.execSQLFiles(f);
		}
	}

	@SuppressWarnings("null")
	private void execSQLFiles(WSqlFile sf) {
		String location = sf.getLocation();
		List<String> infos = sf.getInfos();
		List<String> warns = sf.getWarns();
		List<String> errors = sf.getErrors();
		if(infos == null) {
			infos = new ArrayList<>();
		}

		if(warns == null) {
			warns = new ArrayList<>();
		}

		if(errors == null) {
			errors = new ArrayList<>();
		}

		// 排除: 以debug.sql结尾的都过滤掉
		if(location.toLowerCase().endsWith("debug") || location.toLowerCase().endsWith("error") || location.toLowerCase().endsWith("err")) {
			log.info("debug文件,跳过... {}", location);
			return;
		}
		
		// 列出下级
		File file = new File(location);
		// 在这里肯定是目录
		// File[] subs = file.listFiles();
		File f = file;
		// 现在已经是按文件来了的 不是按目录来了
//		if(subs != null && subs.length > 0) {
//			subs = sort(subs);
//			for(File f : subs) {
				String filename = location;		// + "/" + f.getName();
				// log.info(filename);
//				if(f.isDirectory()) {
//					//log.info("最终执行,是目录,要查下级目录.{}", filename);
//					// execSQLFiles(filename);
//					// 在这里已经不存在可能是按目录来了 20250228
//					log.error("在这里已经不存在可能是按目录来了 20250228");
//				}else {
					// 是文件...
					//log.info("最终执行,是文件:{}", filename);
										
					if(f.lastModified() < BEGINTIME) {
						log.info("文件的最后修改时间早于检查的起始时间,跳过:{}", filename);
						return;
					}

					// 如果不是.sql文件就跳过
					if(!filename.toLowerCase().endsWith(".sql")) {
						log.info("不是SQL文件,跳过:{}", filename);
						return;
					}
					
					// 排除: 以debug.sql结尾的都过滤掉
					if(filename.toLowerCase().endsWith("debug.sql") || filename.toLowerCase().contains("debug")) {
						log.info("debug文件,跳过... {}", filename);
						return;
					}
					try {
						// 编码
						String encoding = EncodingDetect.getJavaEncode(filename).toLowerCase();
						Charset c = Charset.forName(encoding);
						FileReader fr = FileReader.create(f, c);
						// 遇到GO就换文件
						List<String> sqls = new ArrayList<>();
						// sqls.add(fr.readString());
						List<String> temps = new ArrayList<>();
//						if(fr.readString().contains("lp_qry_capacity_weekly")) {
//							Console.log("HERE");
//						}
						Boolean comment = false;
						List<String> lines = fr.readLines();
						if(lines != null) {
							lines.add("\nGO\n");
						}
						for(String line : lines) {
							if(line == null) {
								line = "";
							}
							if(line.contains("/*") && line.contains("*/")) {
								// 用正则表达式,去掉注释中的内容
								line = ReUtil.replaceAll(line, "\\/\\*.*?\\*\\/", "");
							}
							// 如果遇到多行注释开头,则一直等到多行注释结束或者文件末尾
							if(!comment &&  line != null && line.contains("/*")) {
								// 如果是多行注释开头,则一直等到多行注释结束或者文件末尾
								String s = line.substring(0, line.indexOf("/*"));
								temps.add(s);
								comment = true;
								line = " ";
							}
							if(comment && line.contains("*/")) {
								if(line.length() > line.indexOf("*/") + 2) {
									String s = line.substring(line.indexOf("*/") + 2, line.length());
									temps.add(s);
								}
								comment = false;
								line = " ";
							}	
							
							if(comment) {
								continue;
							}
							
							
//							// 把单行注释去掉
//							if(line.contains("--")) {
//								line = line.substring(0, line.indexOf("--"));
//								if(sf.getName().contains("a - 副本.")) {
//									Console.log(line);
//								}
//							}
//							
							if(line != null 
									&& (line.strip().toLowerCase().startsWith("go") || line.strip().toLowerCase().startsWith(PREFIX))
									&& !line.strip().toLowerCase().startsWith("goto")
									//&& ( line.strip().length() < 3 || line.strip().toLowerCase().startsWith("go") )
									) {
								// 换行
								String sql = CollectionUtil.join(temps, System.lineSeparator());
								sqls.add(sql);
								temps = new ArrayList<>();
							}else {
								temps.add(line);
								// temps.add(System.lineSeparator());
							}
						}
						// String texts = CollectionUtil.join(fr.readLines(), System.lineSeparator());
						// .log(texts);
						// Db.update(texts);
						
						long before = System.currentTimeMillis();

						if(sf.getExecBegin() == null) {
							sf.setExecBegin(new Timestamp(before));
						}
						
						for(String sql : sqls) {
							Integer cnt = 0;
							try {
								byte[] bs = sql.getBytes();
								if(bs.length > 3) {
									var b1 = bs[0];
									var b2 = bs[1];
									var b3 = bs[2];
									if(b1 < 0 && b2 < 0 && b3 < 0) {
										// msg += "  [文件头EFBBBF,UTF8-BOM编码]";
										sql = new String(ArrayUtil.sub(bs, 3, bs.length));
									}
								}
								// sql = sql.trim();
								
								cnt = Db.use(CONFIG).update(sql);
								sf.setAffectRowCnt(cnt);
							}catch(Exception e) {
								e.printStackTrace();
								log.error(e);
								String msg = e.getMessage();
								if(msg.contains(":")) {
									msg = msg.substring(msg.indexOf(":") + 1);
								}
								
								
								// SQL文件的开始必然是英文字符, 不能接受中文字符! 因此BYTE里肯定是>0的
								if(msg.contains("附近有语法错误")){		// 可能是UTF8-BOM导致的
									byte[] bs = sql.getBytes();
									if(bs.length > 3) {
										var b1 = bs[0];
										var b2 = bs[1];
										var b3 = bs[2];
										if(b1 < 0 && b2 < 0 && b3 < 0) {
											msg += "  [文件头EFBBBF,UTF8-BOM编码]";
										}
									}
								}
								
								// errors.put( file.getName() + "/" + f.getName(), msg.strip());
								errors.add(msg.strip());
								sf.setDatatype(4);
								
							}
							
							
							
						}
						log.info("执行脚本完成, 脚本名:{},耗时毫秒:{}", filename, (System.currentTimeMillis() - before));
						
						//costs.put(file.getName() + "/" + f.getName(), (System.currentTimeMillis() - before));
						
						Thread.sleep(1);
					}catch(Exception e) {
						e.printStackTrace();
						log.error(e);
						String msg = e.getMessage();
						if(msg.contains(":")) {
							msg = msg.substring(msg.indexOf(":") + 1);
						}
						///errors.put( file.getName() + "/" + f.getName(), msg.strip());
//						if(msg.contains("附近有语法错误")){
//							Console.log("************************");
//						}
						errors.add(msg.strip());
						sf.setDatatype(4);
					}
//				}
//			}
//		}
		sf.setInfos(infos)
		.setWarns(warns)
		.setErrors(errors)
		;
		sf.setExecEnd(DateUtil.getCurrentTimestamp());
	}
	
	/**
	 * 把文件拷贝到本地,放到按年月命名的目录里
	 * @param file
	 */
	private void copytolocal(WSqlFile sf, String location) {
		File file = sf.getFile();
		Timestamp date = sf.getDate();
		// 目录
		if(!FileUtil.exist(location)) {
			FileUtil.mkdir(location);
		}
		// 总目录
		String subfile = location + "/SQLS/";

		if(!FileUtil.exist(subfile)) {
			FileUtil.mkdir(subfile);
		}
		// 分年月目录
		String targetlocation = subfile + "/" + DateUtil.format(date, "yyyyMM") + "/";

		if(!FileUtil.exist(targetlocation)) {
			FileUtil.mkdir(targetlocation);
		}
		
		String fn = targetlocation + "/" + sf.getMd5() + ".sql";
		
		
		if(fn.startsWith("/")) {
			fn = fn.substring(1);
		}
		fn = fn.replace("//", "/");

		if(!FileUtil.exist(fn)) {

			File tc = new File(fn);
			FileUtil.copy(file, tc, false);
			sf.getFileModel().setXLocation(fn);
			sqlfileService.update(sf.getFileModel());
			// sf.getItemModel().setXLocation(targetlocation)
		}
		
	}
	
	
	/**
	 * 检查SQL脚本
	 */
	private List<WSqlFile> checkfiles(Integer mid) {
		// this.listFiles(null);
		// 路径
		URL u = ResourceUtil.getResource(BAKNAME);
		// System.out.println(u);
		String path = u.getPath();
		if(path.startsWith("/")) {
			path = path.substring(1);
		}
		path = URLUtil.decode(path);
		String[] ps = path.split("/");
		// ps = ArrayUtil.sub(ps, 0, ps.length - 1);
		ps[ps.length - 1] = SQLFOLDER;
		
		String location = ArrayUtil.join(ps, "/");	// SQL文件应该放的目录
		
		
		
		
		
		List<WSqlFile> ls = CustomizedReadSQLFileUtil.read(location, new Timestamp(BEGINTIME));
		
		// 首先写到数据库
		if(ls == null) {
			ls = new ArrayList<>();
		}
		Timestamp now = DateUtil.getCurrentTimestamp();
		List<YTSqlCheckerItem> items = new ArrayList<>(ls.size() * 2);
		Integer dID = 1;
		for(WSqlFile f : ls) {
			
			// 要先检查这个脚本是否已登记, 没登记再保存
			YTSqlfile sf = sqlfileService.findByMD5(f.getMd5());
			if(sf == null) {
				sf = new YTSqlfile();
				sf.setXDate(now)
				.setXDateTime(now)
				.setXOwner(f.getOwner())
				.setXName(f.getName())
				.setXObjectName(f.getObjectName())		// 脚本里的对象名
				.setXMD5(f.getMd5())
				.setXStatus(0)
				.setXEncoding(f.getEncoding())
				.setXLen(f.getLen())
				;
				sqlfileService.save(sf);
			}
			f.setId(sf.getSID());
			f.setStatus(sf.getXStatus());
			
			YTSqlCheckerItem item = new YTSqlCheckerItem();
			item.setMID(mid)
			.setRSQLFileID(sf.getSID())
			.setXName(f.getName())
			.setXObjectName(f.getObjectName())
			.setXDataType(0)
			.setXMsg("")
			.setXMD5(f.getMd5())
			//.setXBeginTime()
			//.setXEndTime()
			.setXRowCnt(0)
			.setXLevel(0)
			.setXOwner(f.getOwner())
			.setXEncoding(f.getEncoding())
			.setXDataType(f.getDatatype())
			.setXLocation(f.getLocation())
			.setXDate(f.getDate())
			.setXDateTime(f.getLastModified())
			.setXLen(f.getLen())
			.setDID(dID)
			.setSIndex(dID)
			;
			//sqlCheckerItemService.save(item);
			items.add(item);
			f.setItemModel(item);
			f.setFileModel(sf);
			dID++;

			// 写重复脚本检查数据
			//this.logSQLFiles(f.getName(), f.getLocation(), f.getFolder(), f.getFile());
			
			String upath = u.getPath();
			this.copytolocal(f, upath);
		}
		
		sqlCheckerItemService.save(items);
		
		// 然后检查
		this.checkDumplicateSubmit(mid, ls);
		
		return ls;
	}
	
	@SuppressWarnings("unused")
	public void go() {
		Timestamp erpversion = null;
		Record r = Db.findFirst("select xDateTime from yt_setting where sID = 150001");
		if(r != null && r.getTimestamp("xDateTime") != null) {
			MAXAGE = DateUtil.format(r.getTimestamp("xDateTime"), "yyyyMMdd");
			erpversion = r.getTimestamp("xDateTime");
		}
		long before = System.currentTimeMillis();
		YTSqlChecker bill = new YTSqlChecker();
		Timestamp now = DateUtil.getCurrentTimestamp();
		bill.setXDate(now)
		.setXDateTime(now)
		.setXERPDBVersion(erpversion)
		.setXBeginTime(erpversion)
		.setXEndTime(now)
		.setXFileCnt(0)
		.setXIsOK(false)
		.setXInfoCnt(0)
		.setXWarnCnt(0)
		.setXErrorCnt(0)
		.setXStatus(0)
		.setXStartTime(now)
		.setXFinishTime(now)
		.setXInitCost(0)
		.setXCopyCost(0)
		;
		sqlCheckerService.save(bill);
		
		// 创建目录
		generateFolder();
		
		// 还原数据库
		restoredb();
		bill.setXInitCost(Long.valueOf(System.currentTimeMillis() - before).intValue());
		
		
		before = System.currentTimeMillis();
		copySQLs();
		bill.setXCopyCost(Long.valueOf(System.currentTimeMillis() - before).intValue());
		
		// 初步检查脚本
		List<WSqlFile> ls = this.checkfiles(bill.getSID());
		
		// 执行脚本
		this.execSQLFiles(ls);
		
		// 统计
		// 更新item
		for(WSqlFile f : ls) {
			YTSqlCheckerItem item = f.getItemModel();
			String msg = "";
			Integer level = 0;
			
			List<String> es = f.getErrors();
			List<String> ws = f.getWarns();
			List<String> fs = f.getInfos();
			if(es != null && es.size() > 0) {
				msg += (msg.equals("") ? "" : ";") + CollectionUtil.join(es, ";");
				level = 3;
			}
			if(ws != null && ws.size() > 0) {
				msg += (msg.equals("") ? "" : ";") + CollectionUtil.join(ws, ";");
				if(level == 0) {
					level = 2;
				}
			}
			if(fs != null && fs.size() > 0) {
				msg += (msg.equals("") ? "" : ";") + CollectionUtil.join(fs, ";");
				if(level == 0) {
					level = 1;
				}
			}
			
			if(item != null) {
				item.setXMsg(msg)
				.setXDataType(f.getDatatype() != null ? f.getDatatype() : 1)
				.setXLevel(level)
				.setXBeginTime(f.getExecBegin())
				.setXEndTime(f.getExecEnd())
				.setXRowCnt(f.getAffectRowCnt())
				.setXStatus(level > 0 ? 1 : 0)
				;
				if(f.getStatus() != null && f.getStatus() == 2) {
					item.setXDataType(1)
					.setXStatus(0)
					.setXLevel(0)
					;
					f.getInfos().clear();
					f.getWarns().clear();
					f.getErrors().clear();
				}
				try {
					sqlCheckerItemService.update(item);
				}catch(Exception e) {
					log.error(e);
				}
			}
		}
		
		
		
		
		
		
		
		
		
		
		// 更新主表
		Optional<WSqlFile> op = ls.stream().max(new Comparator<WSqlFile>() {

			@Override
			public int compare(WSqlFile a, WSqlFile b) {
				return Long.compare(a.getLastModified().getTime(), b.getLastModified().getTime());
			}
			
		});
		Boolean ok = true;
		
		Optional<WSqlFile> op2 = ls.stream().filter(new Predicate<WSqlFile>() {

			@Override
			public boolean test(WSqlFile t) {
				return t.getInfos().size() > 0 || t.getWarns().size() > 0 || t.getErrors().size() > 0;
			}
			
		}).findFirst();
		
		Integer infoCnt = 0;
		Integer warnCnt = 0;
		Integer errorCnt = 0;
		for(WSqlFile f : ls) {
			if(f.getStatus() != null && f.getStatus() == 2) {
				continue;
			}
			infoCnt += f.getInfos().size();
			warnCnt += f.getWarns().size();
			errorCnt += f.getErrors().size();
		}
		
		bill.setXFileCnt(ls.size())
		
		.setXIsOK(op2.isEmpty())
		.setXInfoCnt(infoCnt)
		.setXWarnCnt(warnCnt)
		.setXErrorCnt(errorCnt)
		.setXStatus(op2.isEmpty() ? 0 : 1)
		.setXFinishTime(DateUtil.getCurrentTimestamp())
		.setSStatus(5)
		;
		
		if(op.isPresent()) {
			bill.setXEndTime(op.get().getLastModified());
		}
		sqlCheckerService.update(bill);
		
		log.info("检查完成");
		
		
		// 发送消息
		this.sendfeishu(bill.getSID(), false);
		/**
		Console.log(errors);
		Console.log(warnings);
		Console.log(infos);
		**/
		/**
		log.error("**** 脚本检查工具结果 ****");
		log.error("错误级别数:{},警告级别数:{},提示级别数:{}", errors.size(), warnings.size(), infos.size());
		if(infos.size() > 0) {
			log.error("*".repeat(120));
			log.error("提示级别清单:数量{}", infos.size());
			log.error(String.format("%-100s|%-20s", "脚本路径", "异常说明"));
			for(var m : infos.entrySet()) {
				// log.error("{} {} {} {}");
				log.error(String.format("%-100s|%-20s", m.getKey(), m.getValue()));
			}
		}
		if(warnings.size() > 0) {
			log.error("*".repeat(120));
			log.error("警告级别清单:数量{}", warnings.size());
			log.error(String.format("%-100s|%-20s", "脚本路径", "异常说明"));
			for(var m : warnings.entrySet()) {
				log.error(String.format("%-100s|%-20s", m.getKey(), m.getValue()));
			}
		}
		if(errors.size() > 0) {
			log.error("*".repeat(120));
			log.error("错误级别清单:数量{}", errors.size());
			log.error(String.format("%-100s|%-20s", "脚本路径", "异常说明"));
			for(var m : errors.entrySet()) {
				// log.error("{} {} {} {}");
				log.error(String.format("%-100s|%-20s", m.getKey(), m.getValue()));
			}
		}
		**/
		// 生成一个HTML文件 // excel
		// ExcelUtil.
		//writeExcel();
	}

//	
//	public String sendfeishu(Integer mID) {
//		String msg = "";
//		if(FEISHU_CHATID != null && !FEISHU_CHATID.equals("") && ENABLEMSG) {
//			// 消息模板:
//			msg = procService.yp_sqlchecker_report(mID);
//			if(msg != null && msg.length() > 0) {
//				try {
//					feishuMsgBusiness.sendToGroup(FEISHU_CHATID, msg);
//				} catch (Exception e) {
//					log.error(e);
//				}
//			}
//		}else {
//			log.info("没有配置飞书CHATID,不需要发送消息");
//		}
//		return msg;
//	}
	

	/**
	 * 
	 * @param mID
	 * @param force 如果没有问题,force为true时也强制发消息,否则不发
	 * @return
	 */
	public String sendfeishu(Integer mID, Boolean force) {
		String msg = "";
		YTSqlChecker m = sqlCheckerService.find(mID);
		if(FEISHU_CHATID != null && !FEISHU_CHATID.equals("") && m != null && (force || !m.getXIsOK())) {
			// 消息模板:
			msg = procService.yp_sqlchecker_report(mID);
			if(msg != null && msg.length() > 0) {
				try {
					feishuMsgBusiness.sendToGroup(FEISHU_CHATID, msg);
				} catch (Exception e) {
					log.error(e);
				}
			}
		}else {
			log.info("没有配置飞书CHATID,不需要发送消息, 或者检查单据不存在, 或者不需要发送");
		}
		return msg;
	}
	
	
	public static void main(String[] args) {

		new SQLCheckerBusiness().go();
		
	}
	

}
